insert overwrite table jms_dm.dm_order_terminal_dispatch_code_operate_detail_dt partition( dt = '{{ execution_date | cst_ds }}')
select
    WAYBILL_NO                                   -- 运单
    ,substr(PRINT_TIME, 1, 10)                   -- 操作日期
    ,PRINT_TIME                                  -- 操作时间
    ,terminal_dispatch_code                      as old_terminal_dispatch_code
    ,replace(NEW_TERMINAL_DISPATCH_CODE,' ',',') as new_terminal_dispatch_code -- 新三段码
    ,case APPLY_TYPE_CODE
        when 5 then  2  -- 转寄扫描
        when 4 then  3  -- 退件扫描
    end                                          -- 操作事件
    ,PRINT_NETWORK_CODE                          -- 操作网点code
    ,PRINT_NETWORK_NAME                          -- 操作网点名称
    ,PRINT_USER_CODE                             -- 操作人code
    ,PRINT_USER_NAME                             -- 操作人名称
from 
    jms_dwd.dwd_tab_reback_transfer_express_base 
where 
    substr(PRINT_TIME, 1, 10) = '{{ execution_date | cst_ds }}'
    and APPLY_TYPE_CODE in ('4', '5')

union all

select 
    WAYBILL_NO
    ,substr(OPERATION_TIME, 1, 10)                   -- 操作日期
    ,OPERATION_TIME
    ,null
    ,concat_ws(',', FIRST_CODE, SECOND_CODE, THIRD_CODE)
    ,4
    ,OPERATION_NETWORK_CODE
    ,OPERATION_NETWORK_NAME
    ,split(replace(OPERATOR,'|',','),',')[0]
    ,split(replace(OPERATOR,'|',','),',')[1]
from 
    jms_dwd.dwd_t_ass_address_audit_base_dt
where 
    dt = '{{ execution_date | cst_ds }}'
    and substr(OPERATION_TIME, 1, 10) = '{{ execution_date | cst_ds }}'
distribute by pmod(hash(rand()), 3)
;